import openpyxl, re, os
from collections import defaultdict
from typing import Dict, List, Tuple
import logging
from datetime import datetime
import time
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

try:
    def 格式化时间(value):
        return value.strftime("%Y-%m-%d") if isinstance(value, datetime) else value

    def a2c(a: float) -> str:
        n = "零壹贰叁肆伍陆柒捌玖"
        if a < 0:
            return f"负{a2c(-a)}"
        # 先四舍五入到两位小数
        a = round(a, 2)
        i, d = int(a), int(a * 100) % 100  # 保留两位小数
        if not i and not d:
            return "零元整"
        s, p = str(i), []
        # 处理超大数值的单位，每4位一组
        units = ["", "万", "亿", "万亿", "兆"]
        digits = ["", "拾", "佰", "仟"]
        for idx, c in enumerate(reversed(s)):
            group, pos = divmod(idx, 4)
            u = (digits[pos] if pos else units[group]) if idx < 20 else ""
            k = int(c)
            p += [n[k] + u] if k else (['零'] if p and p[-1] != '零' else [])
        t = ''.join(reversed(p)).replace("零万", "万").replace("零亿", "亿").rstrip('零').replace('零零', '零') + ('元' * bool(i))
        j, f = divmod(d, 10)
        e = (f"{n[j]}角" if j > 0 else "") + (f"{n[f]}分" if f > 0 else "整")
        r = f"{t}{e}".replace("元整", "整").replace("零元", "").replace("元元", "元").lstrip("零")
        return r or "零元整"


    def 加载数据(文件路径: str, sheet_name: str = "数据内容") -> Tuple[List[str], Dict[str, List[List]]]:
        wb = openpyxl.load_workbook(文件路径, data_only=True)
        ws = wb[sheet_name] if sheet_name in wb.sheetnames else wb.active
        headers = [cell.value if cell.value is not None else "" for cell in ws[1]]
        data_by_contract = defaultdict(list)
        
        for row in ws.iter_rows(min_row=2):
            values = [cell.value if cell.value is not None else "" for cell in row]
            if not values[0]: continue
            try:
                # 数量保留3位小数
                values[9] = round(float(values[9] or 0.000), 3)  # 数量
                values[17] = round(float(values[17] or 0.00), 2)  # 单价
                values[20] = round(float(values[20] or 0.00), 2)  # 金额
                # 其他数值列保持原有处理
                for idx in [18, 19]:
                    values[idx] = float(values[idx] or 0.0)
                data_by_contract[values[0]].append(values)
            except (TypeError, ValueError) as e:
                logger.error(f"数据转换错误：{values} - {str(e)}")
        
        wb.close()
        return headers, data_by_contract

    def 生成合同文件(合同编号: str, 数据列表: List[List], headers: List[str], 模板路径: str, 输出目录: str) -> None:
        # 修改计算方法：先计算总数量和总金额
        总数量 = sum(row[9] for row in 数据列表)
        总金额 = sum(row[20] for row in 数据列表)
        
        # 新的计算方法：总金额/1.13=不含税金额，总金额-不含税金额=总税额
        总不含税额 = round(总金额 / 1.13, 2)
        总税额 = round(总金额 - 总不含税额, 2)
        
        百分比金额 = 总金额 * 0.2

        基础替换 = {
            "总数量": f"{总数量:.3f}", "总金额大写": a2c(总金额), "总金额": f"{总金额:.2f}",
            "总货款百分比值1": f"{百分比金额:.2f}", "总货款百分比值大写1": a2c(百分比金额),
            "百分比总金额": f"{百分比金额:.2f}", "百分比总金额大写": a2c(百分比金额),
            "总税额": f"{总税额:.2f}", "总不含税金额": f"{总不含税额:.2f}"
        }
        replacements = {f"{{{k}}}": v for k, v in 基础替换.items()}

        条款二_col = headers.index("条款二")
        for seq, row in enumerate(数据列表, 1):
            row_dict = {h: (f"{v:.3f}" if i == 9 else f"{v:.2f}" if i in [17, 20] else 格式化时间(v))
                       for i, (h, v) in enumerate(zip(headers, row))}
            replacements.update({f"{{{k}{seq}}}": v for k, v in row_dict.items()})
            
            clause = row[条款二_col]
            if isinstance(clause, str):
                replacements[f"{{条款二{seq}}}"] = 替换占位符(clause, {**replacements, **{f"{{{k}}}": v for k, v in row_dict.items()}})

        if "合同编号" in headers:
            replacements["{合同编号}"] = str(数据列表[0][headers.index("合同编号")])

        wb = openpyxl.load_workbook(模板路径)
        ws = wb.active
        
        for cell in (cell for row in ws.iter_rows() for cell in row):
            if isinstance(cell.value, str):
                cell.value = 替换占位符(cell.value, replacements)

        for row_num in range(9 + len(数据列表), 24):
            ws.row_dimensions[row_num].hidden = True

        wb.save(os.path.join(输出目录, f"{合同编号}.xlsx"))
        wb.close()
        logger.info(f"生成合同文件：{合同编号}.xlsx 数据量：{len(数据列表)}")
        
    def 替换占位符(text: str, replacements: dict) -> str:
        for _ in range(3):
            new_text = re.sub(r'\{(\w+)\}', lambda m: str(replacements.get(m.group(0), m.group(0))), text)
            if new_text == text: break
            text = new_text
        return text

    def 替换内容(合同内容文件: str, 模板文件: str, 输出目录: str) -> None:
        os.makedirs(输出目录, exist_ok=True)
        [os.remove(os.path.join(输出目录, f)) for f in os.listdir(输出目录) if os.path.isfile(os.path.join(输出目录, f))]
        logger.info(f"输出目录已清空：{输出目录}")
        
        headers, data_by_contract = 加载数据(合同内容文件)
        for 合同编号, 数据列表 in data_by_contract.items():
            生成合同文件(合同编号, 数据列表, headers, 模板文件, 输出目录)

    if __name__ == "__main__":
        替换内容("./批量生成合同内容.xlsx", "./模板/合同模版.xlsx", "./output_files")
except Exception as e:
    print(e)
print("3秒后自动关闭....")
time.sleep(1)
print("2秒后自动关闭....")
time.sleep(1)
print("1秒后自动关闭....")
time.sleep(1)
